package kr.co.design302.readnote.data;

import java.util.ArrayList;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class Query 
{
	private ArrayList<String> imageURL;
	private ArrayList<BookInfo> bookInfoURL;
	private ArrayList<PageInfo> pageInfo;
	private ArrayList<SpanInfo> spanInfoArray;
	private BookInfo info;
	private PageInfo page;
	private SpanInfo span;
	
	public ArrayList<BookInfo> getBookList(SQLiteDatabase db) 
	{	
		bookInfoURL = new ArrayList<BookInfo>();
		Cursor c = db.query(BookListDB.BookList.TABLE_NAME, 
        		new String[] {
					BookListDB.BookList._ID,
					BookListDB.BookList.IMAGE_URL,
					BookListDB.BookList.BOOK_NAME,
					BookListDB.BookList.WRITER_NAME,
					BookListDB.BookList.CREATE_TIME},
				null, null, null, null, BookListDB.BookList.SORT_ORDER);
		
		c.moveToFirst();
		for(int i=0;i<c.getCount();i++) 
		{
			info = new BookInfo();
			info.setBookNumber(c.getInt(c.getColumnIndex(BookListDB.BookList._ID)));
			info.setImageUrl(c.getString(c.getColumnIndex(BookListDB.BookList.IMAGE_URL)));
			info.setBookName(c.getString(c.getColumnIndex(BookListDB.BookList.BOOK_NAME)));
			info.setBookAuthor(c.getString(c.getColumnIndex(BookListDB.BookList.WRITER_NAME)));
			bookInfoURL.add(info);
			
			c.moveToNext();
		}
		c.close();
		return bookInfoURL;
	}
	
	public void addBookItem(SQLiteDatabase db, String getImageUrl, String getBookName, String getBookAuthor, String currentTime)
	{
		db.execSQL("INSERT INTO " + BookListDB.BookList.TABLE_NAME + 
				"(" + BookListDB.BookList.IMAGE_URL +
				", " + BookListDB.BookList.BOOK_NAME +
				", " + BookListDB.BookList.WRITER_NAME +
				", " + BookListDB.BookList.CREATE_TIME +
				") VALUES('" + getImageUrl + "', '" + getBookName + "', '" + getBookAuthor + "', '" + currentTime + "');");
	}
	
	
	public void deleteBook(SQLiteDatabase db, String imgUrl) 
	{
		db.execSQL("DELETE FROM " + BookListDB.BookList.TABLE_NAME + " WHERE " + BookListDB.BookList.IMAGE_URL + "='" + imgUrl +"';");
	}
	
	public void addPageData(SQLiteDatabase db, int bookNum, int pageNum, String pageText) 
	{
		db.execSQL("INSERT INTO " + BookListDB.PageList.TABLE_NAME + "("
				+ BookListDB.PageList.BOOK_NUMBER + ", "
				+ BookListDB.PageList.PAGE_NUMBER + ", "
				+ BookListDB.PageList.PAGE_CONTENT + ") VALUES ("
				+ bookNum + ", " + pageNum + ", '" + pageText + "');");
	}
	
	public void updatePageData(SQLiteDatabase db, int bookNum, int pageNum, String pageText)
	{
		db.execSQL("UPDATE " + BookListDB.PageList.TABLE_NAME + " SET "
				+ BookListDB.PageList.BOOK_NUMBER + "=" + bookNum + ", "
				+ BookListDB.PageList.PAGE_NUMBER + "=" + pageNum + ", "
				+ BookListDB.PageList.PAGE_CONTENT + "='" + pageText + "';");
	}
	
	public ArrayList<PageInfo> getPageList(SQLiteDatabase db, int bookNumber) 
	{
		pageInfo = new ArrayList<PageInfo>();
		Cursor c = db.rawQuery("SELECT * FROM " + BookListDB.PageList.TABLE_NAME +
				" WHERE " + BookListDB.PageList.BOOK_NUMBER + "=" + bookNumber + 
				" ORDER BY " + BookListDB.PageList.PAGE_NUMBER + " DESC;", null);
		c.moveToFirst();
		for(int i=0;i<c.getCount();i++) 
		{
			page = new PageInfo();
			page.setBookNumber(c.getInt(c.getColumnIndex(BookListDB.PageList.BOOK_NUMBER)));
			page.setPageNumber(c.getInt(c.getColumnIndex(BookListDB.PageList.PAGE_NUMBER)));
			page.setPageContent(c.getString(c.getColumnIndex(BookListDB.PageList.PAGE_CONTENT)));
			pageInfo.add(page);
			c.moveToNext();
		}
		c.close();
		return pageInfo;
	}
	
	public int getBookNumber(SQLiteDatabase db, String bookName, String writer, String imageUrl)
	{
		int bookId = 0;
		Cursor c = db.rawQuery("SELECT " + BookListDB.BookList._ID +" FROM " + BookListDB.BookList.TABLE_NAME +
				" WHERE " + BookListDB.BookList.BOOK_NAME + "='" + bookName + 
				"' AND " + BookListDB.BookList.WRITER_NAME + "='" + writer + 
				"' AND " + BookListDB.BookList.IMAGE_URL + "='" + imageUrl + "';", null);
		c.moveToFirst();
		if(c.getCount() == 1)
		{
			bookId = c.getInt(c.getColumnIndex(BookListDB.BookList._ID));
		}
		c.close();
		return bookId;
	}
	
	
	/**
	 * 
	 */
	public void setSpanQuery(SQLiteDatabase db, int bookNumber, int pageNumber, int style, int start, int end)
	{
		db.execSQL("INSERT INTO " + BookListDB.SpanList.TABLE_NAME + "("
				+ BookListDB.SpanList.BOOK_NUMBER + ", "
				+ BookListDB.SpanList.PAGE_NUMBER + ", "
				+ BookListDB.SpanList.SPAN_STYLE + ", "
				+ BookListDB.SpanList.SPAN_START + ", "
				+ BookListDB.SpanList.SPAN_END + ") VALUES ("
				+ bookNumber + ", " + pageNumber + ", " + style + ", " + start + ", " + end + ");");
	}
	
	public ArrayList<SpanInfo> getSpanQuery(SQLiteDatabase db, int bookNumber, int pageNumber)
	{
		spanInfoArray = new ArrayList<SpanInfo>();
		Cursor c = db.rawQuery("SELECT * FROM " + BookListDB.SpanList.TABLE_NAME + " WHERE " 
				+ BookListDB.SpanList.BOOK_NUMBER + "=" + bookNumber + " AND "
				+ BookListDB.SpanList.PAGE_NUMBER + "=" + pageNumber + ";", null);
		c.moveToFirst();
		for(int i=0;i<c.getCount();i++)
		{
			span = new SpanInfo(c.getInt(c.getColumnIndex(BookListDB.SpanList.SPAN_START)),
					c.getInt(c.getColumnIndex(BookListDB.SpanList.SPAN_END)), 
					c.getInt(c.getColumnIndex(BookListDB.SpanList.SPAN_STYLE)));
			spanInfoArray.add(span);
			c.moveToNext();
		}
		
		c.close();
		
		return spanInfoArray;
	}
}